<?php
class mdl_upload_excel extends CI_Model{ 
	
	var $tabel = 'tm_kecelakaan_diri';
	
	function __construct(){
		parent::__construct();
	} 

	function data($p=array()){
	
		$where = '';
		
		if( isset($p['column']) && isset($p['keyword']) ){
			if($p['column'] != '' || $p['keyword'] != ''){
				if($p['column'] == 'all'){
					$where = " AND ( kd_name like '%".pTxt($p['keyword'])."%' 
									 OR pa_name like '%".pTxt($p['keyword'])."%')";
				}else{				
					$where = " AND ".$p['column']." like '%".pTxt($p['keyword'])."%' ";
				}
			}
		}
		
		if( isset($p['start_date']) && trim($p['start_date']) !="" ){
				
			$where .=" AND date_format(a.time_add,'%Y-%m-%d') >= '".dbClean($p['start_date'])."'";
		}
		
		if( isset($p['end_date']) && trim($p['end_date']) !="" ){
			$where .=" AND date_format(a.time_add,'%Y-%m-%d') <= '".dbClean($p['end_date'])."'";
		}
		
		$sql = "
		 	select 
				SQL_CALC_FOUND_ROWS *,a.time_add as tgl_entry
			FROM ".$this->tabel." a, tm_pialang_client_pa b
			WHERE a.kd_pa = b.pa_id
				  AND a.kd_status = 1
				".$where."
			ORDER BY kd_id DESC 
		"; 
		
		if( isset($p['limit']) && isset($p['offset']) ){
			$offset = empty($p['offset'])?0:$p['offset'];				
			$sql .= " LIMIT ".$offset.",".$p['limit']." ";
		}
		$query = $this->db->query($sql);
		
		$found_rows = $this->db->query('SELECT FOUND_ROWS() as found_rows');

		return array(
			"data" 	=> $query->result(),
			"count"	=> $found_rows->row()
		);
	}	
	
	function data_detail($p=array()){
	
		$where = '';
		
		if( isset($p['column']) && trim($p['column']) !="" ){				
			$where .=" AND a.kd_pa = '".dbClean($p['column'])."'";
		}
		
		
		if( isset($p['start_date']) && trim($p['start_date']) !="" ){				
			$where .=" AND date_format( cast(concat(a.kd_bulan,'-01') as date) ,'%Y-%m') >= '".dbClean($p['start_date'])."'";
		}
		
		if( isset($p['end_date']) && trim($p['end_date']) !="" ){
			$where .=" AND date_format( cast(concat(a.kd_bulan,'-01') as date) ,'%Y-%m') <= '".dbClean($p['end_date'])."'";
		}
		
		$tbl = "tm_kecelakaan_diri";$tbl2 = "tm_kd_detail";
		$sql = "
		 	select 
				SQL_CALC_FOUND_ROWS *
			FROM ".$tbl." a, ".$tbl2." b
			WHERE a.kd_id = b.detail_kd 
				".$where."
			ORDER BY a.kd_id DESC 
		";	 
		
		if( isset($p['limit']) && isset($p['offset']) ){
			$offset = empty($p['offset'])?0:$p['offset'];				
			$sql .= " LIMIT ".$offset.",".$p['limit']." ";
		}
		$query = $this->db->query($sql);
		
		$found_rows = $this->db->query('SELECT FOUND_ROWS() as found_rows');

		return array(
			"data" 	=> $query->result(),
			"count"	=> $found_rows->row()
		);
	}	
	
}